﻿Public Class perFrete
    Inherits perAcessoBD

    ReadOnly Property sqlConsulta() As String
        Get
            Dim sSql As String
            sSql = "    SELECT CodFrete as Código, descItinerario as [Itinerário]"
            sSql &= "     FROM Frete"
            sSql &= " GROUP BY codFrete, descItinerario"
            sSql &= " ORDER BY descItinerario"
            Return sSql
        End Get
    End Property

    ReadOnly Property sqlConsultaExpedicao(ByVal sCodFrete As String) As String
        Get
            Dim sSql As String
            sSql = "  SELECT tipoExp as Código, tipoVeiculo as [Veículo], convert(numeric(10,2), valor) as Valor"
            sSql &= "  FROM Frete"
            sSql &= " WHERE RTrim(LTrim(codFrete)) = '" & sCodFrete & "'"
            sSql &= " ORDER BY tipoExp"
            Return sSql
        End Get
    End Property

    Public Function retornarDestItinerario(ByVal sCodFrete As String) As String
        Dim sSql As String
        Dim dtRetorno As DataTable
        Dim sRetorno As String = ""

        Try
            sSql = "SELECT descItinerario " &
                   "  FROM Frete " &
                   " WHERE RTrim(LTrim(codFrete)) = @codFrete"

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@codFrete", sCodFrete.Trim)
            End With

            dtRetorno = MyBase.executarConsulta(sSql)

            If dtRetorno.Rows.Count > 0 Then
                sRetorno = Conversao.nuloParaVazio(dtRetorno.Rows(0).Item("descItinerario"))
            End If

            Return sRetorno

        Catch ex As Exception
            Throw New Exception("Ocorreu um erro ao selecionar os dados do frete." & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Function retornarExpedicao(ByVal sCodFrete As String,
                                      ByVal iCodExpedicao As Integer) As DataTable
        Dim sSql As String

        Try
            sSql = "SELECT tipoExp, tipoVeiculo, Valor " &
                   "  FROM Frete " &
                   " WHERE RTrim(LTrim(codFrete)) = @codFrete" &
                   "   AND tipoExp = @codExpedicao"

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@codFrete", sCodFrete.Trim)
                .AddWithValue("@codExpedicao", iCodExpedicao)
            End With

            Return MyBase.executarConsulta(sSql)

        Catch ex As Exception
            Throw New Exception("Ocorreu um erro ao selecionar os dados do frete." & Environment.NewLine & ex.Message)
        End Try

    End Function

End Class
